package com.bafa.dbcache;

import java.sql.ResultSet;

import com.bafa.jdbc.DBAccess;
import com.bafa.jdbc.QueryObject;

import com.bafa.objects.schema.*;

public class GrabDivision {

	
	public static void grabDivision(DBAccess db, Season season)throws Exception{
		ObjectFactory factory = new ObjectFactory();
		QueryObject divisionQuery = new QueryObject("league");
		divisionQuery.addOrderBy("division",false);
		divisionQuery.setDistinct(true);
		divisionQuery.addSelectFields("division");
		divisionQuery.addWhereAndField("seasonId",""+season.getID());
		
		ResultSet divisionResults = db.query(divisionQuery);
		while(divisionResults.next()){
			Division division = factory.createDivision();
			division.setDivision(divisionResults.getInt("division"));
			division.setSeasonId(season.getID());
			
			/**********************Grab teams*************************/
			grabTeams(division,db);
			/*********************************************************/
			
			/**********************Grab Matches***********************/
			grabMatches(division,db);
			/*********************************************************/
			
			/**********************Grab Division Rules****************/
			grabDivisionRules(db,division);
			/*********************************************************/
			
			//*********************Grab Division Top Scorers************
			grabDivisionTopScorers(db,division);
			//**********************************************************
			
			//*********************Grab is Division Completed***********
			grabIsDivisionComplete(db,division);
			//**********************************************************
			
			season.getDivision().add(division);
		}
	}
	
    static Season grabDivision(DBAccess db, Season season, int divisionId)throws Exception{
            ObjectFactory factory = new ObjectFactory();
            QueryObject divisionQuery = new QueryObject("league");
            divisionQuery.addWhereAndField("division",divisionId+"");
            divisionQuery.addOrderBy("division",false);
            divisionQuery.setDistinct(true);
            divisionQuery.addSelectFields("division");
            divisionQuery.addWhereAndField("seasonId",""+season.getID());
            
            ResultSet divisionResults = db.query(divisionQuery);
            
            while(divisionResults.next()){
                    Division division = season.getDivision().get(divisionId-1);
                    
                    
                    /**********************Grab teams*************************/
                    grabTeams(division,db);
                    /*********************************************************/
                    
                    /**********************Grab Matches***********************/
                    grabMatches(division,db);
                    /*********************************************************/
                    
                    /**********************Grab Division Rules****************/
                    grabDivisionRules(db,division);
                    /*********************************************************/
                    
                    //*********************Grab Division Top Scorers************
                    grabDivisionTopScorers(db,division);
                    //**********************************************************
                    
                    //*********************Grab is Division Completed***********
                    grabIsDivisionComplete(db,division);
                    //**********************************************************
            }
            return season;
    }
    
	public static String grabTeamInPos(int seasonId, DBAccess db, int divisionId, int position)throws Exception{
		ResultSet leagueResults = grabTeamsSQL(db,divisionId,seasonId);
		int i = 1;
		while(leagueResults.next()){
			if(i==position){
				ResultSet teamResult = GrabTeams.grabTeam(db,leagueResults.getInt("teamId"));
				while(teamResult.next()){
					return teamResult.getString("Name");
				}
			}
			i++;
		}
		return "";
	}
	
	public static int grabTeamIdInPos(int seasonId, DBAccess db, int divisionId, int position)throws Exception{
		ResultSet leagueResults = grabTeamsSQL(db,divisionId,seasonId);
		int i = 1;
		while(leagueResults.next()){
			if(i==position){
				ResultSet teamResult = GrabTeams.grabTeam(db,leagueResults.getInt("teamId"));
				while(teamResult.next()){
					return teamResult.getInt("id");
				}
			}
			i++;
		}
		return 0;
	}
	
	private static ResultSet grabTeamsSQL(DBAccess db, int divisionId, int seasonId)throws Exception{
		QueryObject leagueQuery = new QueryObject("league");
		leagueQuery.addOrderBy("points",true);
		leagueQuery.addOrderBy("goalsfor-goalsagainst",false);
		leagueQuery.addOrderBy("goalsfor",true);
		leagueQuery.addSelectFields("teamId");
		leagueQuery.addWhereAndField("seasonid",seasonId+"");
		leagueQuery.addWhereAndField("division",divisionId+"");
		
		return db.query(leagueQuery);
		
	}
	public static void grabTeams(Division division,DBAccess db)throws Exception{
		ResultSet leagueResults = grabTeamsSQL(db,division.getDivision(),division.getSeasonId());
		division.getTeam().clear();
		while(leagueResults.next()){
			
			division.getTeam().add(leagueResults.getInt("teamId"));
		}
	}
	
	
	public static void grabDivisionTopScorers(DBAccess db,Division division)throws Exception{
		ObjectFactory factory = new ObjectFactory();
		QueryObject scorersQuery = new QueryObject("scorers");
		scorersQuery.addOrderBy("scorername",false);
		scorersQuery.setDistinct(true);
		scorersQuery.addSelectFields("scorername");
		scorersQuery.addWhereAndField("seasonid",division.getSeasonId()+"");
		scorersQuery.addWhereAndField("divisionid",division.getDivision()+"");
		
		ResultSet scorersResults = db.query(scorersQuery);
		while(scorersResults.next()){
			QueryObject scorerQuery = new QueryObject("scorers");
			scorerQuery.addWhereAndField("seasonid",division.getSeasonId()+"");
			scorerQuery.addWhereAndField("divisionid",division.getDivision()+"");
			scorerQuery.addWhereAndField("scorername","'"+scorersResults.getString("scorername")+"'");
			
			ResultSet scorerResults = db.query(scorerQuery);
			int goals = 0;
			int team = 0;
			while(scorerResults.next()){
				team = scorerResults.getInt("teamid");
				goals=goals+scorerResults.getInt("NumberOfGoals");
			}
			Scorer scorer = factory.createScorer();
			scorer.setName(scorersResults.getString("scorername"));
			scorer.setGoals(goals);
			scorer.setTeamId(team);
			scorer.setSeasonId(division.getSeasonId());
			division.getScorer().add(scorer);
		}
	}
	private static void grabMatches(Division division,DBAccess db)throws Exception{
		QueryObject matchesQuery = new QueryObject("matches");
		matchesQuery.addOrderBy("date",false);
		matchesQuery.addOrderBy("time",false);
		matchesQuery.addSelectFields("id");
		matchesQuery.addWhereAndField("seasonid",division.getSeasonId()+"");
		matchesQuery.addWhereAndField("division",division.getDivision()+"");
		
		ResultSet matchesResults = db.query(matchesQuery);
		while(matchesResults.next()){
			division.getMatch().add(matchesResults.getInt("id"));
		}
	}
	
	private static void grabDivisionRules(DBAccess db,Division division)throws Exception{
		ObjectFactory factory = new ObjectFactory();
		QueryObject queryRules = new QueryObject("leaguerules");
		queryRules.addWhereAndField("SeasonId",""+division.getSeasonId());
		queryRules.addWhereAndField("DivisionId",""+division.getDivision());
		ResultSet leagueRulesResults = db.query(queryRules);
		
		while(leagueRulesResults.next()){
			LeagueRules leaguerules = factory.createLeagueRules();
			leaguerules.setNumberOfTeamsPromoted(leagueRulesResults.getInt("Promoted"));
			leaguerules.setNumberOfTeamsRelegated(leagueRulesResults.getInt("Relegated"));
			leaguerules.setNumberOfTimesEachTeamPlays(leagueRulesResults.getInt("NumToPlayEachTeam"));
			division.setLeagueRules(leaguerules);
		}
		
	}
	
	private static void grabIsDivisionComplete(DBAccess db, Division division)throws Exception{
		division.setComplete(isDivisionComplete(db,division.getSeasonId(),division.getDivision()));
		
	}
	
	public static boolean isDivisionComplete(DBAccess db,int seasonId, int divisionId)throws Exception{
		QueryObject seasonCompletedQuery = new QueryObject("matches");
		seasonCompletedQuery.addWhereAndField("SeasonId",""+seasonId);
		seasonCompletedQuery.addWhereAndField("Division",""+divisionId);
		seasonCompletedQuery.addWhereAndField("Played","1");
		
		ResultSet seasonCompletedResults = db.count(seasonCompletedQuery);
		
		QueryObject seasonTotalQuery = new QueryObject("matches");
		seasonTotalQuery.addWhereAndField("SeasonId",""+seasonId);
		seasonTotalQuery.addWhereAndField("Division",""+divisionId);
		
		ResultSet seasonTotalResults = db.count(seasonTotalQuery);
		
		while(seasonCompletedResults.next()&&seasonTotalResults.next()){
			if(seasonCompletedResults.getInt(1)==seasonTotalResults.getInt(1))
				return true;
		}
		
		return false;
	}
}
